package com.yyg.usecase.mysql;


import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.Objects;

import static org.junit.Assert.assertEquals;

public class MysqlURLTimeZoneTest {

    // JDBC连接URL
    static final String JDBC_URL = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
    // 数据库用户名
    static final String USER = "root";
    // 数据库密码
    static final String PASSWORD = "root";

    // 在 setUp() 方法中加载 JDBC 驱动
    // Connection 和 Statement 对象
    private Connection conn;
    private Statement stmt;

    // 在 setUp() 方法中获取 Connection 和 Statement 对象
    @Before
    public void setUp() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
        stmt = conn.createStatement();
    }

    @Test
    public void testCreateTable() throws SQLException {
        // 创建表
        String createTableSQL =
                "create table if not exists `test` (\n" +
                        "  `id` int unsigned not null auto_increment primary key,\n" +
                        "  `varchar_` varchar(255),\n" +
                        "  `bigint_` bigint  default 0,\n" +
                        "  `timestamp_` timestamp  default current_timestamp,\n" +
                        "  `datetime_` datetime  default current_timestamp\n" +
                        ")engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;";
        stmt.executeUpdate(createTableSQL);
    }

    //测试删除表
    @Test
    public void testDeleteTable() throws SQLException {
        // 连接到数据库
        try (Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
             // 创建 Statement 对象
             Statement stmt = conn.createStatement()) {
            // 删除表
            String deleteTableSQL = "DROP TABLE IF EXISTS test";
            int rowsAffected = stmt.executeUpdate(deleteTableSQL);
        }
    }

    @Test
    public void testConnectionTimeZone() throws SQLException {
        /**
         * 结论： 连接URL中的 connectionTimeZone 是用于解释 SQL server端返回的时间字符串的。也就是说SQL server只返回了时间字符串， 不会返回时区信息， 而是客户端通过 connectionTimeZone 参数来指定时区。
         * ----
         * 现象: url不指定 connectionTimeZone 用原始的statement写sql插入北京时间2024-04-13T00:00:00+08:00 到timestamp_，查询并打印timestamp.toString(), java打印出来是 2024-04-12 16:00:00.0，
         * ----
         * 原因在于：插入时 client 2024-04-13T00:00:00+08:00 -> server端是UTC时间，自然存储的是2024-04-12 16:00:00，
         * 查询时 server端返回的是2024-04-12 16:00:00字符串， jdbc不是根据server端的时区来解释这个字符串，而是根据 url connectionTimeZone 字段来解释这个字符串（connectionTimeZone没有值，默认是当前操作系统时区UTC+8）
         * 2024-04-12 16:00:00字符串被当做UTC+8时区解释成2024-04-12 16:00:00+08:00，所以导致我们插入了一个2024-04-13T00:00:00+08:00 但是查询变成了2024-04-12 16:00:00
         * ----
         * 注意： connectionTimeZone 只有当查询时间时才会被jdbc消费
         */
        testDeleteTable();
        testCreateTable();
        //插入北京时区的日期字符串
        Connection connUTC8 = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", USER, PASSWORD);
        Statement stmtUTC8 = connUTC8.createStatement();
        String insertDataSQL = "insert into `test` (`timestamp_`) values ('2024-04-13T00:00:00+08:00');";
        stmtUTC8.executeUpdate(insertDataSQL);
        System.out.println("成功插入 2024-04-13T00:00:00+08:00 到timestamp类型字段");

        ResultSet resultSetUTC8 = stmtUTC8.executeQuery("select id, timestamp_ from test");
        System.out.println("connectionTimeZone缺失(UTC+8)查询的记录如下:");
        printResultSet(resultSetUTC8);

        //用UTC时区（0时区）解释SQL SERVER端返回的时间字符串
        Connection connUTC = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?connectionTimeZone=UTC", USER, PASSWORD);
        Statement stmtUTC = connUTC.createStatement();
        ResultSet resultSetUTC = stmtUTC.executeQuery("select id, timestamp_ from test");
        System.out.println("\nconnectionTimeZone=UTC查询的记录如下:");
        printResultSet(resultSetUTC);

    }

    @Test
    public void testForceConnectionTimeZoneToSession() throws SQLException {
        /**
         * 结论： 连接URL中的 forceConnectionTimeZoneToSession=true 是用于描述当sql语句插入时间字符串时，该时间字符串在server端应该转换为serverTimeZone时区进行存储。
         * ----
         * 举个例子：（下面的例子forceConnectionTimeZoneToSession都置为true）
         * case1. 插入时间字符串 2024-04-15T00:00:00+08:00(带时区，UTC+8) 到timestamp类型字段，如果serverTimeZone=UTC+8，则插入数据库中的时间是 2024-04-15T00:00:00+08:00，
         *   case1.1 如果serverTimeZone=UTC，则插入数据库中的时间是 2024-04-14T16:00:00+00:00(注意mysql server端做了时区转换，将UTC+8的0点转换为UTC前一天的16点)。
         *      {@link com.yyg.usecase.mysql.MysqlURLTimeZoneTest#testForceConnectionTimeZoneToSessionCase1_1()}
         * case2. 插入时间字符串 2024-04-15 00:00:00(不带时区) 到timestamp类型字段，如果 serverTimeZone=UTC+8，则插入数据库中的时间是 2024-04-15T00:00:00+08:00 (UTC+8作为字符串的时区)，
         *  case2.1如果 serverTimeZone=UTC，则插入数据库中的时间是 2024-04-15T00:00:00+00:00（UTC作为字符串的时区，注意这里mysql server端没有做任何时区转换，强行把serverTimeZone时区作为时间字符串的时区）。
         *  也就是说当插入不带时区的时间字符串时，mysql server端会将serverTimeZone作为时间字符串的时区.
         *  ----
         * 注意：serverTimeZone只有插入时间字符串才会被消费， 含义是插入时间时，服务端应该将时间字符串转换为该时区的时间（如果时间字符串带时区信息，则转换到serverTimeZone,
         * 如果时间字符串没有带时区信息，则serverTimeZone作为默认时区），然后存储到数据库中。
         */
        testDeleteTable();
        testCreateTable();
        // case1.插入带时区的时间字符串 2024-04-15T00:00:00+08:00 serverTimeZone=Asia/Shanghai(UTC+8) forceConnectionTimeZoneToSession=true
        String insertDataSQL = "insert into `test` (`timestamp_`) values ('2024-04-15T00:00:00+08:00');";
        // 这里设置了serverTimeZone=Asia/Shanghai(UTC+8) ， forceConnectionTimeZoneToSession=true 在server端会将2024-04-15T00:00:00+08:00 转换为UTC+8的时区（因为Asia/Shanghai就是UTC+8,所以实际没有转换），然后插入数据库中。
        Connection connUTC8 = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimeZone=Asia/Shanghai&forceConnectionTimeZoneToSession=true", USER, PASSWORD);
        Statement stmtUTC8 = connUTC8.createStatement();
        stmtUTC8.executeUpdate(insertDataSQL);
        //这里请在mysql控制台查看，输入 set time_zone = '+8:00'; select id, timestamp_ from test; 数据库中的时间是 2024-04-15 00:00:00
        //java中打印是看不到mysql里面实际存的时间的，因为jdbc会把server端的时间转换为本地jvm时间.
        closeConnAndStmt(connUTC8, stmtUTC8);


    }
    @Test
    public void testForceConnectionTimeZoneToSessionCase1_1() throws SQLException {
        testDeleteTable();
        testCreateTable();
        // case1.1 插入带时区的时间字符串 2024-04-15T00:00:00+08:00 serverTimeZone=UTC forceConnectionTimeZoneToSession=true
        String insertDataSQLUTC = "insert into `test` (`timestamp_`) values ('2024-04-15T00:00:00+08:00');";
        // 这里设置了serverTimeZone=UTC ， 在server端会将2024-04-15T00:00:00+08:00 转换为UTC时区时间（2024-04-14T16:00:00+00:00）
        Connection connUTC = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimeZone=UTC&forceConnectionTimeZoneToSession=true", USER, PASSWORD);
        Statement stmtUTC = connUTC.createStatement();
        stmtUTC.executeUpdate(insertDataSQLUTC);
        //这里请在mysql控制台查看，输入 set time_zone = '+0:00'; select id, timestamp_ from test; 数据库中的时间是 2024-04-14 16:00:00
        closeConnAndStmt(connUTC, stmtUTC);
    }



    //打印ResultSet
    private void printResultSet(ResultSet resultSet) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            System.out.print(metaData.getColumnName(i) + "\t\t\t");
        }
        System.out.println();
        while (resultSet.next()) {
            for (int i = 1; i <= columnCount; i++){
                if (Objects.equals(metaData.getColumnName(i),"timestamp_")) {
                    System.out.print(resultSet.getTimestamp(i) + "\t\t\t");
                } else if (Objects.equals(metaData.getColumnName(i), "datetime_")) {
                    System.out.print(resultSet.getTimestamp(i) + "\t\t\t");
                } else {
                    System.out.print(resultSet.getString(i) + "\t\t\t");
                }

            }
        }
    }


    @After
    // 在测试方法执行完成后关闭连接
    public void tearDown() throws SQLException {
        closeConnAndStmt(conn, stmt);
    }

    public void closeConnAndStmt(Connection conn, Statement stmt) throws SQLException {
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
        System.out.println("closeConnAndStmt invoked!");
    }

}
